﻿using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class RenyuanDAL
    {
        //string str = "server = SC-201709031915; database = RenLiZiYuanGuanli; uid = sa ; pwd = 123456";
        string str = "server = LAPTOP-KE3OLTQM; database = RenLiZiYuanGuanli; uid = sa ; pwd = 123456";
        //员工管理显示
        public List<Staff> YGxinxi()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {

                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand comd = new SqlCommand("RYxinxi", coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                comd.CommandType = CommandType.StoredProcedure;
                DataTable tb = new DataTable();
                dap.Fill(tb);
                List<Staff> list = new List<Staff>();
                foreach (DataRow item in tb.Rows)
                {
                    Staff m = new Staff();
                    m.Staff_ID = Convert.ToInt32(item["Staff_ID"]);
                    m.Staff_Name = item["Staff_Name"].ToString();
                    m.Staff_Sex = Convert.ToInt32(item["Staff_Sex"]);
                    m.Staff_Age = Convert.ToInt32(item["Staff_Age"]);
                    m.Staff_Money = Convert.ToDecimal(item["Staff_Money"]);
                    m.Staff_Address = item["Staff_Address"].ToString();
                    m.Staff_GL = Convert.ToInt32(item["Staff_GL"]);
                    m.Train = Convert.ToInt32(item["Train"]);
                    m.Hiredate =item["Hiredate"].ToString();
                    m.BuMen = item["BuMen"].ToString();
                    m.RenyuanZT = Convert.ToInt32(item["RenyuanZT"]);
                    m.JishuTechang = item["JishuTechang"].ToString();
                    m.Image_Url = item["Image_Url"].ToString();
                    list.Add(m);
                }
                try
                {
                    return list;
                }
                catch (Exception)
                {

                    throw;
                }
            }
        }
        ///人员信息修改
        public int updateRen(Staff m)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                string sql = string.Format("update Staff set Staff_Name='{0}',Staff_Age='{1}',Staff_Sex='{2}',Staff_Address='{3}',Staff_Money='{4}',Staff_GL='{5}',Hiredate='{6}',Train='{7}',BuMen='{8}',RenyuanZT='{9}',JishuTechang='{10}',Image_Url='{11}' where Staff_ID='{12}'",
                    m.Staff_Name,m.Staff_Age,m.Staff_Sex,m.Staff_Address,m.Staff_Money,m.Staff_GL,m.Hiredate,m.Train,m.BuMen,m.RenyuanZT,m.JishuTechang,m.Image_Url,m.Staff_ID);
                SqlCommand comd = new SqlCommand(sql, coon);
               
                try
                {
                    return comd.ExecuteNonQuery();
                }
                catch (Exception )
                {

                    return 0;
                }
                finally {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
        ///人员信息添加
        public int RenAdd(Models.Staff user)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("RenAdd", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = { 
                                         new SqlParameter("@Name",SqlDbType.VarChar,50),
                                         new SqlParameter("@Age",SqlDbType.Int),
                                         new SqlParameter("@Sex",SqlDbType.Int),
                                         new SqlParameter("@zhuzhi",SqlDbType.VarChar,100),
                                         new SqlParameter("@gongzi",SqlDbType.Decimal),
                                         new SqlParameter("@gongling",SqlDbType.Int),
                                         new SqlParameter("@Rzdate",SqlDbType.VarChar,100),
                                         new SqlParameter("@Peixun",SqlDbType.Int),
                                         new SqlParameter("@Bumen",SqlDbType.VarChar,100),
                                         new SqlParameter("@ZT",SqlDbType.Int),
                                         new SqlParameter("@Techang",SqlDbType.VarChar,50),
                                         new SqlParameter("@Images",SqlDbType.VarChar,500)
                                      };
                pars[0].Value = user.Staff_Name;
                pars[1].Value = user.Staff_Age;
                pars[2].Value = user.Staff_Sex;
                pars[3].Value = user.Staff_Address;
                pars[4].Value = user.Staff_Money;
                pars[5].Value = user.Staff_GL;
                pars[6].Value = user.Hiredate;
                pars[7].Value = user.Train;
                pars[8].Value = user.BuMen;
                pars[9].Value = user.RenyuanZT;
                pars[10].Value = user.JishuTechang;
                pars[11].Value = user.Image_Url;

                command.Parameters.AddRange(pars);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
        /// <summary>
        /// 查询人员信息
        /// </summary>
        /// <param name="Staff_Name"></param>
        /// <returns></returns>
        public List<Staff> cha(string Staff_Name)
        {
            
        using(SqlConnection coon=new SqlConnection(str))
        {
            string sql = string.Format("select * from Staff where Staff_Name like '%{0}%' ",Staff_Name);
            SqlCommand comd = new SqlCommand(sql,coon);
            SqlDataAdapter dap = new SqlDataAdapter(comd);
            DataTable tb = new DataTable();
            List<Staff> list = new List<Staff>();
            dap.Fill(tb);
            foreach (DataRow item in tb.Rows)
            {
                 Staff w = new Staff();
                    w.Staff_ID = Convert.ToInt32(item["Staff_ID"]);
                    w.Staff_Name = item["Staff_Name"].ToString();
                    w.Staff_Sex = Convert.ToInt32(item["Staff_Sex"]);
                    w.Staff_Age = Convert.ToInt32(item["Staff_Age"]);
                    w.Staff_Money = Convert.ToDecimal(item["Staff_Money"]);
                    w.Staff_Address = item["Staff_Address"].ToString();
                    w.Staff_GL = Convert.ToInt32(item["Staff_GL"]);
                    w.Train = Convert.ToInt32(item["Train"]);
                    w.Hiredate =item["Hiredate"].ToString();
                    w.BuMen = item["BuMen"].ToString();
                    w.RenyuanZT = Convert.ToInt32(item["RenyuanZT"]);
                    w.JishuTechang = item["JishuTechang"].ToString();
                    w.Image_Url = item["Image_Url"].ToString();
                    list.Add(w);
            }
            try
            {
                  return list;
            }
            catch (Exception)
            {
                
                throw;
            }

        }
        }
        /// <summary>
        /// 提交奖惩
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int Jc(奖惩 m)
        { 
        using(SqlConnection coon=new SqlConnection(str))
        {
            coon.Open();
            string sql = string.Format("insert into 奖惩管理(姓名,部门,担任职务,奖惩区分,日期,金额,原因,提交人意见,人事意见) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",m.姓名,m.部门,m.担任职务,m.奖惩区分,m.日期,m.金额,m.原因,m.提交人意见,m.人事意见);
            SqlCommand comd = new SqlCommand(sql,coon);
           return comd.ExecuteNonQuery();
        }
        }
        /// <summary>
        /// 档案显示
        /// </summary>
        /// <returns></returns>
        public List<DangAn> DangAnIndex()
        {

            using (SqlConnection coon = new SqlConnection(str))
            {
                string sql = "select * from DangAn inner join Staff on DangAn.Staff_ID=Staff.Staff_ID";
                SqlCommand comd = new SqlCommand(sql, coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                DataTable tb = new DataTable();
                List<DangAn> list = new List<DangAn>();
                dap.Fill(tb);
                foreach (DataRow item in tb.Rows)
                {
                    DangAn w = new DangAn();
                    w.DangAn_ID = Convert.ToInt32(item["DangAn_ID"]);
                    w.Staff_ID = Convert.ToInt32(item["Staff_ID"]);
                    w.DangAn_Name = item["DangAn_Name"].ToString();
                    w.DangAn_Beizhu = item["DangAn_Beizhu"].ToString();
                    w.Staff_Name = item["Staff_Name"].ToString();
                    w.Staff_Sex = Convert.ToInt32(item["Staff_Sex"]);
                    w.Staff_Age = Convert.ToInt32(item["Staff_Age"]);
                    w.Staff_Money = Convert.ToDecimal(item["Staff_Money"]);
                    w.Staff_Address = item["Staff_Address"].ToString();
                    w.Staff_GL = Convert.ToInt32(item["Staff_GL"]);
                    w.Train = Convert.ToInt32(item["Train"]);
                    w.Hiredate = item["Hiredate"].ToString();
                    w.BuMen = item["BuMen"].ToString();
                    w.RenyuanZT = Convert.ToInt32(item["RenyuanZT"]);
                    w.JishuTechang = item["JishuTechang"].ToString();
                    w.DangAn_JYJL = item["DangAn_JYJL"].ToString();
                    w.DangAn_PeiXunJL = item["DangAn_PeiXunJL"].ToString();
                    w.DangAN_Ziwo = item["DangAN_Ziwo"].ToString();
                    list.Add(w);
                }
                try
                {
                    return list;
                }
                catch (Exception)
                {

                    throw;
                }

            }
        }
        /// <summary>
        /// 档案详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DangAn DnagAnFT(int id)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                string sql = string.Format("select * from DangAn inner join Staff on DangAn.Staff_ID=Staff.Staff_ID where DangAn_ID='{0}'", id);
                SqlCommand comd = new SqlCommand(sql,coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                DataTable tb = new DataTable();
                dap.Fill(tb);
                DangAn m = new DangAn();
                m.DangAn_ID = Convert.ToInt32(tb.Rows[0]["DangAn_ID"]);
                m.Staff_Name = tb.Rows[0]["Staff_Name"].ToString() ;
                m.Staff_Sex = Convert.ToInt32(tb.Rows[0]["Staff_Sex"]);
                m.Staff_Age = Convert.ToInt32(tb.Rows[0]["Staff_Age"]);
                m.BuMen = tb.Rows[0]["BuMen"].ToString();
                m.JishuTechang = tb.Rows[0]["JishuTechang"].ToString();
                m.Staff_Address = tb.Rows[0]["Staff_Address"].ToString();
                m.DangAn_BiYe = tb.Rows[0]["DangAn_BiYe"].ToString();
                m.DangAn_NeiRong = tb.Rows[0]["DangAn_NeiRong"].ToString();
                m.DangAn_JYJL = tb.Rows[0]["DangAn_JYJL"].ToString();
                m.DangAn_PeiXunJL = tb.Rows[0]["DangAn_PeiXunJL"].ToString();
                m.DangAN_Ziwo = tb.Rows[0]["DangAN_Ziwo"].ToString();
                return m;
                
             
            }
        }
        /// <summary>
        /// 添加人员档案
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int DangAnAdd(DangAn m)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();
                string sql = string.Format("insert into DangAn values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",m.Staff_ID,m.DangAn_Name,m.DangAn_NeiRong,m.DangAn_Beizhu,m.DangAn_BiYe,m.DangAn_JYJL,m.DangAn_PeiXunJL,m.DangAN_Ziwo);
                SqlCommand comd = new SqlCommand(sql, coon);
                return comd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 删除人员档案
        /// </summary>
        /// <param name="DangAn_ID"></param>
        /// <returns></returns>
        public int DangAnDel(string id)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();
                string sql = string.Format("delete from DangAn where DangAn_ID in ({0}) ",id);
                SqlCommand comd = new SqlCommand(sql, coon);
                return comd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 几条奖惩信息未处理
        /// </summary>
        /// <returns></returns>
        public int Tiaoshu()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();
                string sql = " select count(1) from 奖惩管理 where Zt=0";
                SqlCommand comd = new SqlCommand(sql,coon);
               int i= Convert.ToInt32(comd.ExecuteScalar());
               return i;
            }
        }
        /// 合同显示
        public List<Contracts> ContractsIndex()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();
                string sql = "select * from Contracts inner join Staff on Contracts.Staff_ID=Staff.Staff_ID";
                SqlCommand comd = new SqlCommand(sql,coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                DataTable tb = new DataTable();
                List<Contracts> list = new List<Contracts>();
                dap.Fill(tb);
                foreach (DataRow item in tb.Rows)
                {
                    Contracts m = new Contracts();
                    m.Contracts_ID = Convert.ToInt32(item["Contracts_ID"]);
                    m.Staff_Name = item["Staff_Name"].ToString();
                    m.Startdate = Convert.ToDateTime(item["Startdate"]);
                    m.Deadline = Convert.ToDateTime(item["Deadline"]);
                    list.Add(m);
                }
                return list;

            }
            
        }
        //查看合同详情
        public Contracts ContractsFT(int id)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();
                string sql = string.Format("select * from Contracts inner join Staff on Contracts.Staff_ID=Staff.Staff_ID where Contracts_ID='{0}'", id);
                SqlCommand comd = new SqlCommand(sql, coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                DataTable tb = new DataTable();
                dap.Fill(tb);
                Contracts m = new Contracts();
                m.Contracts_ID = Convert.ToInt32(tb.Rows[0]["Contracts_ID"]);
                m.Staff_Name = tb.Rows[0]["Staff_Name"].ToString();
                m.Neirong = tb.Rows[0]["Neirong"].ToString();
                m.BuMen = tb.Rows[0]["BuMen"].ToString();
                m.Deadline = Convert.ToDateTime(tb.Rows[0]["Deadline"]);
                m.Startdate = Convert.ToDateTime(tb.Rows[0]["Startdate"]);
                return m;

            }
        }
        //添加员工合同
        public int ContractsAdd(Contracts m)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();
                string sql = string.Format("insert into Contracts values('{0}','{1}','{2}','{3}') ", m.Staff_ID, m.Startdate, m.Deadline, m.Neirong);
                SqlCommand comd = new SqlCommand(sql,coon);
               return comd.ExecuteNonQuery();
            }
        }
        //奖惩信息显示
        public List<奖惩> Jcindex()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();
                string sql ="select * from 奖惩管理 where Zt=0";
                SqlCommand comd = new SqlCommand(sql,coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                DataTable tb = new DataTable();
                dap.Fill(tb);
                List<奖惩> m = new List<奖惩>();
                foreach (DataRow item in tb.Rows)
                {
                    奖惩 j = new 奖惩();
                    j.ID = Convert.ToInt32(item["ID"]);
                    j.姓名 = item["姓名"].ToString();
                    j.部门 = item["部门"].ToString();
                    j.奖惩区分 = item["奖惩区分"].ToString();
                    j.金额 = Convert.ToDecimal(item["金额"]);
                    j.日期 = item["日期"].ToString();
                    j.原因 = item["原因"].ToString();
                    j.提交人意见 = item["提交人意见"].ToString();
                    j.人事意见 = item["人事意见"].ToString();
                    j.Zt = Convert.ToInt32(item["Zt"]);
                    m.Add(j);

                }
                return m;
            }
        }
        //审批详情
        public 奖惩 Jcshenpi(int id)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();
                string sql = string.Format("select * from 奖惩管理  where ID='{0}'", id);
                SqlCommand comd = new SqlCommand(sql, coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                DataTable tb = new DataTable();
                dap.Fill(tb);
                奖惩 m = new 奖惩();
                m.ID = Convert.ToInt32(tb.Rows[0]["ID"]);
                m.姓名 = tb.Rows[0]["姓名"].ToString();
                m.部门 = tb.Rows[0]["部门"].ToString();
                m.担任职务 = tb.Rows[0]["担任职务"].ToString();
                m.金额 = Convert.ToDecimal(tb.Rows[0]["金额"]);
                m.原因 = tb.Rows[0]["原因"].ToString();
                m.提交人意见 = tb.Rows[0]["提交人意见"].ToString();
                m.人事意见 = tb.Rows[0]["人事意见"].ToString();
                m.奖惩区分 = tb.Rows[0]["奖惩区分"].ToString();
                return m;

            }
        }
        ///审批通过
        public int TG(int id)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();
                
                string sql = string.Format("update 奖惩管理 set Zt=1, TG=1 where ID='{0}'", id);
                SqlCommand comd = new SqlCommand(sql,coon);
               return comd.ExecuteNonQuery();
                
            }

        }
        ///审批拒绝
        public int JJ(int id)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                coon.Open();

                string sql = string.Format("update 奖惩管理 set Zt=1, TG=0 where ID='{0}'", id);
                SqlCommand comd = new SqlCommand(sql, coon);
                return comd.ExecuteNonQuery();

            }

        }
        //奖惩显示
        public List<奖惩> JiangchengIndex()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                string sql = "select * from 奖惩管理";
                SqlCommand comd = new SqlCommand(sql,coon);
                SqlDataAdapter dap = new SqlDataAdapter(comd);
                DataTable tb = new DataTable();
                dap.Fill(tb);
                List<奖惩> list = new List<奖惩>();
                foreach (DataRow item in tb.Rows)
                {
                    奖惩 m = new 奖惩();
                    m.ID = Convert.ToInt32(item["ID"]);
                    m.姓名 = item["姓名"].ToString();
                    m.部门 = item["部门"].ToString();
                    m.担任职务 = item["担任职务"].ToString();
                    m.奖惩区分 = item["奖惩区分"].ToString();
                    m.日期 = item["日期"].ToString();
                    m.金额 = Convert.ToDecimal(item["金额"]);
                    m.原因 = item["原因"].ToString();
                    m.提交人意见 = item["提交人意见"].ToString();
                    m.人事意见 = item["人事意见"].ToString();
                    m.Zt = Convert.ToInt32(item["Zt"]);
                    m.TG = Convert.ToInt32(item["TG"]);
                    list.Add(m);

                }
                return list;
            }
            
        }

    }
}
